﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using KlauserswagModel;

namespace WebShopDAL
{
    public class OrderRowDB
    {
        private static KlauserswagEntities _context = new KlauserswagEntities();
        //Create:
        /// <summary>
        /// Method for inserting a new OrderRow.
        /// </summary>
        /// <param name="orderRow">The OrderRow to be inserted into the Database</param>
        /// <returns>Returns 1 if everything went well.</returns>
        public static int InsertNewOrderRow(OrderRow orderRow)
        {
            try
            {
                //var query = _context.OrderRows.Where(o => o.OrderRowID == orderRow.OrderRowID).ToList().First();

                _context.OrderRows.Add(orderRow);
                return _context.SaveChanges();

                //using (var connection = ConnectionDB.GetConnection())
                //{
                //    if (connection == null)
                //        throw new Exception("Connection is null in OrderRowDB, InsertNewOrderRow");

                //    var sqlQuery = new StringBuilder("INSERT INTO [dbo].[OrderRow]([OrderID],[Quantity],[ArticleID]) ");
                //    sqlQuery.Append("VALUES(@OrderID,@Quantity,@ArticleID) ");

                //    using (var command = new SqlCommand(sqlQuery.ToString(), connection))
                //    {
                //        command.Parameters.Add("OrderID", SqlDbType.BigInt).Value = orderRow.OrderId;
                //        command.Parameters.Add("Quantity", SqlDbType.BigInt).Value = orderRow.Quantity;
                //        command.Parameters.Add("ArticleID", SqlDbType.BigInt).Value = orderRow.ArticleId;

                //        int affectedRows = command.ExecuteNonQuery();
                //        return affectedRows;
                //    }
                //}
            }
            catch (Exception ex)
            {
                throw new KlauserSwagException("Something went wrong in OrderRowDB, InsertNewOrderRow", ex);
            }
        }

        //Read:
        /// <summary>
        /// Method for retrieving a OrderRow on a specific ID.
        /// </summary>
        /// <param name="orderRowId">The ID of the OrderRow to retrieve</param>
        /// <returns>Returns the OrderRow on the input ID</returns>
        public static OrderRow GetOrderRow(int orderRowId)
        {
            try
            {
                return _context.OrderRows.Where(o => o.OrderRowID == orderRowId).ToList().First();

                //using (var connection = ConnectionDB.GetConnection())
                //{
                //    if (connection == null)
                //        throw new Exception("Connection is null in OrderRowDB, GetOrderRow");

                //    var sqlQuery = new StringBuilder("SELECT [OrderRowID],[OrderID],[Quantity],[ArticleID] ");
                //    sqlQuery.Append("FROM [dbo].[OrderRow] ");
                //    sqlQuery.Append("WHERE [OrderRowID] = @OrderRowID ");

                //    using (var command = new SqlCommand(sqlQuery.ToString(), connection))
                //    {
                //        command.Parameters.Add("OrderRowID", SqlDbType.BigInt).Value = orderRowId;

                //        var adapter = new SqlDataAdapter(command);
                //        var dataSet = new DataSet();
                //        adapter.Fill(dataSet);

                //        bool orderRowFound = dataSet.Tables[0].Rows.Count == 1;

                //        if (orderRowFound)
                //        {
                //            return new OrderRow(orderRowId: orderRowId,
                //                orderId: int.Parse(dataSet.Tables[0].Rows[0]["OrderID"].ToString().Trim()),
                //                quantity: int.Parse(dataSet.Tables[0].Rows[0]["Quantity"].ToString().Trim()),
                //                articleId: int.Parse(dataSet.Tables[0].Rows[0]["ArticleID"].ToString().Trim())
                //                );
                //        }
                //        else
                //        {
                //            return null;
                //        }
                //    }
                //}
            }
            catch (Exception ex)
            {
                throw new KlauserSwagException("Something went wrong in OrderRowDB, GetOrderRow", ex);
            }
        }

        /// <summary>
        /// Method for retrieving all OrderRows on a specific OrderId.
        /// </summary>
        /// <param name="orderId">The OrderID to retrieve all OrderRows from.</param>
        /// <returns>Returns a List with all OrderRows in.</returns>
        public static List<OrderRow> GetAllOrderRowsOnOrderId(int orderId)
        {
            try
            {
                return _context.OrderRows.Where(o => o.OrderID == orderId).ToList();

                //using (var connection = ConnectionDB.GetConnection())
                //{
                //    if (connection == null)
                //        throw new Exception("Connection is null in OrderRowDB, GetAllOrderRowsOnOrderId");

                //    var sqlQuery = new StringBuilder("SELECT * FROM [dbo].[OrderRow] ");
                //    sqlQuery.Append("WHERE [OrderID] = @OrderID ");


                //    using (var command = new SqlCommand(sqlQuery.ToString(), connection))
                //    {
                //        command.Parameters.Add("OrderID", SqlDbType.BigInt).Value = orderId;

                //        var adapter = new SqlDataAdapter(command);
                //        var dataSet = new DataSet();
                //        adapter.Fill(dataSet);

                //        bool orderRowsFound = dataSet.Tables[0].Rows.Count > 0;

                //        if (!orderRowsFound)
                //            return null;

                //        var orderRows = new List<OrderRow>();

                //        for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++)
                //        {
                //            orderRows.Add(new OrderRow(
                //                orderRowId: int.Parse(dataSet.Tables[0].Rows[i]["OrderRowID"].ToString()),
                //                orderId: int.Parse(dataSet.Tables[0].Rows[i]["OrderID"].ToString()),
                //                quantity: int.Parse(dataSet.Tables[0].Rows[i]["Quantity"].ToString()),
                //                articleId: int.Parse(dataSet.Tables[0].Rows[i]["ArticleID"].ToString())
                //                ));
                //        }
                //        return orderRows;
                //    }
                //}
            }
            catch (Exception ex)
            {
                throw new KlauserSwagException("Something went wrong in OrderRowDB, GetAllOrderRowsOnOrderId", ex);
            }
        }

        //Update:
        /// <summary>
        /// Method for updating a OrderRow.
        /// </summary>
        /// <param name="orderRow">The OrderRow to update.</param>
        /// <returns>Returns 1 if everything went well.</returns>
        public static int UpdateOrderRow(OrderRow orderRow)
        {
            try
            {
                var query = _context.OrderRows.Where(o => o.OrderRowID == orderRow.OrderRowID);
                return _context.SaveChanges();

                //using (var connection = ConnectionDB.GetConnection())
                //{
                //    if (connection == null)
                //        throw new Exception("Connection is null in OrderRowDB, UpdateOrderRow");

                //    var sqlQuery = new StringBuilder("UPDATE [dbo].[OrderRow]");
                //    sqlQuery.Append("SET [OrderID] = @OrderId, [Quantity] = @Quantity, [ArticleID] = @ArticleId ");
                //    sqlQuery.Append("WHERE [OrderRowID] = @OrderRowId");

                //    using (var command = new SqlCommand(sqlQuery.ToString(), connection))
                //    {
                //        command.Parameters.Add("OrderRowID", SqlDbType.BigInt).Value = orderRow.OrderRowId;
                //        command.Parameters.Add("OrderId", SqlDbType.BigInt).Value = orderRow.OrderId;
                //        command.Parameters.Add("Quantity", SqlDbType.BigInt).Value = orderRow.Quantity;
                //        command.Parameters.Add("ArticleId", SqlDbType.BigInt).Value = orderRow.ArticleId;

                //        int affectedRows = command.ExecuteNonQuery();
                //        return affectedRows;
                //    }
                //}
            }
            catch (Exception ex)
            {
                throw new KlauserSwagException("Something went wrong in OrderRowDB, UpdateOrderRow", ex);
            }
        }

        //Delete:       
        /// <summary>
        /// Method for deleting an orderrow on a specific orderRowID.
        /// </summary>
        /// <param name="orderRowId">The ID of the OrderRow to remove</param>
        /// <returns>Returns 1 if everything went ok.</returns>
        public static int DeleteOrderRow(int orderRowId)
        {
            try
            {
                var query = _context.OrderRows.Where(o => o.OrderRowID == orderRowId);
                return _context.SaveChanges();

                //using (var connection = ConnectionDB.GetConnection())
                //{
                //    if (connection == null)
                //        throw new Exception("Connection is null in OrderRowDB, DeleteOrderRow");

                //    var sqlQuery = new StringBuilder("DELETE FROM [dbo].[OrderRow] WHERE [OrderRowID] = @OrderRowID");

                //    using (var command = new SqlCommand(sqlQuery.ToString(), connection))
                //    {
                //        command.Parameters.Add("OrderRowID", SqlDbType.BigInt).Value = orderRowId;

                //        int affectedRows = command.ExecuteNonQuery();
                //        return affectedRows;
                //    }
                //}
            }
            catch (Exception ex)
            {
                throw new KlauserSwagException("Something went wrong in OrderRowDB, DeleteOrderRow", ex);
            }
        }
    }
}
